package com.wonders.frame.chron.dao.card.impl;

import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class CardDaoImpl {
	@PersistenceContext
	protected EntityManager em;

	@Transactional  
	@SuppressWarnings("unchecked")
	public int rebindCatalog(Integer pIdNew, List<Integer> cardIds, String code) {
		int ret = 0;
		try {
			String sql = "update AF_RELATION "
					+ "set P_ID = :pIdNew "
					+ "where REMOVED = 0 and N_ID in :cardIds and P_TYPE = 'catalog' and N_TYPE = 'card'";
			Query query = em.createNativeQuery(sql);
			query.setParameter("pIdNew", pIdNew).setParameter("cardIds", cardIds);
			query.executeUpdate();
			
			sql = "update AF_CH_CARD set CODE = :code where ID in :cardIds";
			query = em.createNativeQuery(sql);
			query.setParameter("code", code).setParameter("cardIds", cardIds);
			query.executeUpdate();
			
			ret = 1;
		} catch (Exception e) {
			
		} finally {
			if (em != null) {
				em.close();
			}
		}
		return ret;
	}

	public String getSerialNo(String deptId) {
		String ret = "";
		try {
			String sql = "select max(DEPT_CODE) \"deptCode\", count(b.id) \"no\" "
					+ "from AF_ORGAN_CODE a left join AF_CH_CARD b on (b.CARD_NO like '%-'||a.DEPT_CODE||'-%') "
					+ "where a.REMOVED = 0 and a.DEPT_ID = :deptId";
			Query query = em.createNativeQuery(sql);
			query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); 
			query.setParameter("deptId", deptId);
			List<Map> list = query.getResultList();
			if(list != null && list.size() > 0){
				Map m = list.get(0);
				String serial = String.format("%05d", Integer.valueOf(m.get("no").toString())+1);
				
				ret = "SZ-"+m.get("deptCode")+"-"+serial;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (em != null) {
				em.close();
			}
		}
		
		return ret;
	}
	
	@Transactional
	@SuppressWarnings("unchecked")
	public int bindSummary(Integer summaryId, List<Integer> cardIds) {
		try {
			String sql = "update AF_CH_CARD "
					+ "set SUMMARY_ID = :summaryId "
					+ "where REMOVED = 0 and ID in :cardIds ";
			Query query = em.createNativeQuery(sql);
			query.setParameter("summaryId", summaryId).setParameter("cardIds", cardIds);
			return query.executeUpdate();
		} catch (Exception e) {
			return 0;
		} finally {
			if (em != null) {
				em.close();
			}
		}
	}	
}

